﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Xugege.AbpExtension.Com.Extension.TypeExtend;
using OfficeOpenXml;

namespace Xugege.AbpExtension.Application.Extension.DataImport.Excel.EpPlus
{
    public class EpPlusExcelImporter
    {

        #region 从Excel中加载数据（泛型）+IEnumerable<T> LoadFromExcel<T>(string FileName) where T : new()
        /// <summary>
        /// 从Excel中加载数据（泛型）
        /// </summary>
        /// <typeparam name="T">每行数据的类型</typeparam>
        /// <param name="fileName">Excel文件名</param>
        /// <param name="prop">
        /// 属性对照表 例如：{"类型中的名称":"excel中的名称","name":"名称"}
        /// </param>
        /// <returns>泛型列表</returns>
        public static IEnumerable<T> LoadFromExcel<T>(string fileName, IDictionary<string, string> prop) where T : new()
        {
            FileInfo existingFile = new FileInfo(fileName);
            List<T> resultList = new List<T>();
            Dictionary<string, int> dictHeader = new Dictionary<string, int>();

            using (ExcelPackage package = new ExcelPackage(existingFile))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[1];

                int colStart = worksheet.Dimension.Start.Column;  //工作区开始列
                int colEnd = worksheet.Dimension.End.Column;       //工作区结束列
                int rowStart = worksheet.Dimension.Start.Row;       //工作区开始行号
                int rowEnd = worksheet.Dimension.End.Row;       //工作区结束行号

                //将每列标题添加到字典中
                for (int i = colStart; i <= colEnd; i++)
                {
                    dictHeader[worksheet.Cells[rowStart, i].Value.ToString()] = i;
                }

                List<PropertyInfo> propertyInfoList = new List<PropertyInfo>(typeof(T).GetProperties());

                for (int row = rowStart + 1; row <= rowEnd; row++)
                {
                    T result = new T();

                    //为对象T的各属性赋值
                    foreach (PropertyInfo p in propertyInfoList)
                    {
                        try
                        {
                            ExcelRange cell = worksheet.Cells[row, dictHeader[prop[p.Name]]]; //与属性名对应的单元格

                            if (cell.Value == null)
                                continue;

                            var v = TypeConverterExtension.ConvertObject(cell.Value, p.PropertyType);
                            p.SetValue(result, v);
                            //switch (p.PropertyType.Name.ToLower())
                            //{
                            //    case "string":
                            //        p.SetValue(result, cell.GetValue<String>());
                            //        break;
                            //    case "int16":
                            //        p.SetValue(result, cell.GetValue<Int16>());
                            //        break;
                            //    case "int32":
                            //        p.SetValue(result, cell.GetValue<Int32>());
                            //        break;
                            //    case "int64":
                            //        p.SetValue(result, cell.GetValue<Int64>());
                            //        break;
                            //    case "decimal":
                            //        p.SetValue(result, cell.GetValue<Decimal>());
                            //        break;
                            //    case "double":
                            //        p.SetValue(result, cell.GetValue<Double>());
                            //        break;
                            //    case "datetime":
                            //        p.SetValue(result, cell.GetValue<DateTime>());
                            //        break;
                            //    case "boolean":
                            //        p.SetValue(result, cell.GetValue<Boolean>());
                            //        break;
                            //    case "byte":
                            //        p.SetValue(result, cell.GetValue<Byte>());
                            //        break;
                            //    case "char":
                            //        p.SetValue(result, cell.GetValue<Char>());
                            //        break;
                            //    case "single":
                            //        p.SetValue(result, cell.GetValue<Single>());
                            //        break;
                            //    default:
                            //        break;
                            //}
                        }
                        catch (KeyNotFoundException ex)
                        { }
                    }
                    resultList.Add(result);
                }
            }
            return resultList;
        }
        #endregion
    }
}
